package com.sinodata.ns.kqgl;

import jt.classic.system.ISystem;
import jt.classic.system.SimpleAction;
import jt.classic.system.database.SystemDataSource;
import jt.classic.system.group.GroupCenter;
import jt.classic.system.group.IGroup;
import jt.classic.system.user.IUser;
import jt.classic.system.user.UserManager;
import jt.classic.system.utils.AnnexTool;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import org.limp.basework.impl.CommonBean4HSQ;

import org.limp.mine.DataTrimmer;
import org.limp.mine.DateTrimmer;
import org.limp.mine.StringTool;
import org.limp.mine.annex.AnnexInfo;

import java.sql.Connection;

import java.text.SimpleDateFormat;

import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


/**
 * 考勤统计
 *
 * @author mido
 *
 */
public class KQStatAction extends SimpleAction {
    /**
     * 日考勤统计
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward daystat(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        Connection conn = null;

        CommonBean4HSQ req = new CommonBean4HSQ(request);

        Map param = req.getResource();

        String day = StringTool.checkString(param.get("day"));

        if (day.equals("")) {
            day = DateTrimmer.getYMD();
        } else {
            day = DateTrimmer.formatDateForVarchar(day);
        }

        request.setAttribute("day", day);

        IUser user = ISystem.getSessionUser(request);

        String groupId = StringTool.checkString(param.get("groupId"));

        if (groupId.equals("")) {
            groupId = user.igroup().id();
            request.setAttribute("group", user.igroup());
        } else {
            GroupCenter gc = GroupCenter.getInstance();
            IGroup group = gc.getGroupById(groupId);

            request.setAttribute("group", group);
        }

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            DataTrimmer trimmer = new DataTrimmer(conn);

            UserManager um = new UserManager(conn);

            /*监理人员信息*/
            List users = um.getUsersInGroup(groupId);

            request.setAttribute("users", users);

            /*考勤签到信息*/
            String sql = "SELECT * FROM APP_NS_KQ WHERE K_GROUPID = '" +
                groupId + "' AND K_CTIME LIKE '%" + day +
                "%' AND K_TYPE = 0 ORDER BY K_CTIME DESC";
            Map qiandaoMap = trimmer.searchSymmetricalMap(sql, "K_USERID");

            if (!qiandaoMap.isEmpty()) {
                AnnexTool annexTool = new AnnexTool(conn);

                Iterator keys = qiandaoMap.keySet().iterator();

                while (keys.hasNext()) {
                    String key = (String) keys.next();
                    Map info = (Map) qiandaoMap.get(key);
                    String id = (String) info.get("K_ID");

                    AnnexInfo[] annexs = annexTool.getAnnexs(id, "APP_NS_KQ");

                    info.put("K_ZHAOPIAN", annexs);
                }
            }

            request.setAttribute("qiandaoMap", qiandaoMap);

            /*考勤签退信息*/
            String sql1 = "SELECT * FROM APP_NS_KQ WHERE K_GROUPID = '" +
                groupId + "' AND K_CTIME LIKE '%" + day +
                "%' AND K_TYPE = 1 ORDER BY K_CTIME ASC";
            Map qiantuiMap = trimmer.searchSymmetricalMap(sql1, "K_USERID");

            if (!qiantuiMap.isEmpty()) {
                AnnexTool annexTool = new AnnexTool(conn);

                Iterator keys = qiantuiMap.keySet().iterator();

                while (keys.hasNext()) {
                    String key = (String) keys.next();
                    Map info = (Map) qiantuiMap.get(key);
                    String id = (String) info.get("K_ID");

                    AnnexInfo[] annexs = annexTool.getAnnexs(id, "APP_NS_KQ");

                    info.put("K_ZHAOPIAN", annexs);
                }
            }

            request.setAttribute("qiantuiMap", qiantuiMap);
        } catch (Exception ex) {
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return mapping.findForward("daystat");
    }

    /**
     * 月考勤统计
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward monthstat(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        Connection conn = null;

        CommonBean4HSQ req = new CommonBean4HSQ(request);

        Map param = req.getResource();

        String day = StringTool.checkString(param.get("day"));

        if (day.equals("")) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");

            Calendar lastDate = Calendar.getInstance();
            lastDate.set(Calendar.DATE, 1); //设为当前月的1号  
            lastDate.add(Calendar.MONTH, -1); //减一个月，变为下月的1号  

            /*上月所属年*/
            day = sdf.format(lastDate.getTime());
        }

        request.setAttribute("day", day);

        IUser user = ISystem.getSessionUser(request);

        String groupId = StringTool.checkString(param.get("groupId"));

        if (groupId.equals("")) {
            groupId = user.igroup().id();
            request.setAttribute("group", user.igroup());
        } else {
            GroupCenter gc = GroupCenter.getInstance();
            IGroup group = gc.getGroupById(groupId);

            request.setAttribute("group", group);
        }

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            DataTrimmer trimmer = new DataTrimmer(conn);

            UserManager um = new UserManager(conn);

            /*监理人员信息*/
            List users = um.getUsersInGroup(groupId);

            request.setAttribute("users", users);

            /*考勤签到信息*/
            String sql =
                "SELECT K_USERID, COUNT(b.K_CTIME) AS CNT FROM (SELECT a.* FROM (SELECT K_USERID, substring(K_CTIME, 1,  8) AS K_CTIME FROM APP_NS_KQ WHERE K_GROUPID = '" +
                groupId + "' AND K_CTIME LIKE '%" + day +
                "%' AND K_TYPE = 0) a GROUP BY a.K_USERID, a.K_CTIME) b GROUP BY b.K_USERID";
            Map qiandaoMap = trimmer.searchSymmetricalMap(sql, "K_USERID");

            request.setAttribute("qiandaoMap", qiandaoMap);

            /*考勤签退信息*/
            String sql1 =
                "SELECT K_USERID, COUNT(b.K_CTIME) AS CNT FROM (SELECT a.* FROM (SELECT K_USERID, substring(K_CTIME, 1,  8) AS K_CTIME FROM APP_NS_KQ WHERE K_GROUPID = '" +
                groupId + "' AND K_CTIME LIKE '%" + day +
                "%' AND K_TYPE = 1) a GROUP BY a.K_USERID, a.K_CTIME) b GROUP BY b.K_USERID";
            Map qiantuiMap = trimmer.searchSymmetricalMap(sql1, "K_USERID");

            request.setAttribute("qiantuiMap", qiantuiMap);
        } catch (Exception ex) {
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return mapping.findForward("monthstat");
    }
}
